﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using DVSProject.Models;
using DVSProject.Lib;

namespace DVSProject.Lib
{
    public class EmployeeRepo
    {
        private const string key = "yaservnet";
        public const string m_msg_head = "Employee";
        public  Crypto _crypto = new Crypto();
        public IEnumerable<EmployeeModels> GetEmployee()
        {
            List<EmployeeModels> Employees = new List<EmployeeModels>();
            string SQLCommand = @"select TBEmployee.*,TBDepartment.dept_name_th from TBEmployee LEFT JOIN TBDepartment
                                  ON TBEmployee.dept_id = TBDepartment.dept_id WHERE TBEmployee.Status <> 'Delete'";
            using (var db = new DBHelper())
            {
                try
                {
                    var reader = db.ExecuteReader(SQLCommand);
                    while (reader.Read())
                    {
                        EmployeeModels _EmployeeModels = new EmployeeModels();

                        _EmployeeModels.EmpId = (string)reader["EmpId"];
                        _EmployeeModels.NameTh = (string)reader["NameTh"];
                        _EmployeeModels.NameEng = (string)reader["NameEng"];
                        _EmployeeModels.AddressTh = (string)reader["AddressTh"];
                        _EmployeeModels.AddressEng = (string)reader["AddressEng"];
                        _EmployeeModels.Tel = (string)reader["Tel"];
                        _EmployeeModels.dept_id = (int)reader["dept_id"];
                        _EmployeeModels.dept_name_th = (string)reader["dept_name_th"];
                        _EmployeeModels.UserName = (string)reader["UserName"];
                        _EmployeeModels.Password = (string)reader["Password"];
                        _EmployeeModels.Status = (string)reader["Status"];
                        _EmployeeModels.CreateBy = (string)reader["CreateBy"];
                        _EmployeeModels.CreateOn = (DateTime)reader["CreateOn"];
                        _EmployeeModels.UpdateBy = (string)reader["UpdateBy"];
                        _EmployeeModels.UpdateOn = (DateTime)reader["UpdateOn"];

                        Employees.Add(_EmployeeModels);
                    }
                }
                catch (Exception ex)
                {
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0001", ex));
                }
            }
            return Employees.ToList();
        }

        public EmployeeModels GetEmployeeById(string id)
        {
            EmployeeModels _EmployeeModels = new EmployeeModels();

            string SQLCommand = @"select TBEmployee.*,TBDepartment.dept_name_th from TBEmployee LEFT JOIN TBDepartment
                                ON TBEmployee.dept_id = TBDepartment.dept_id where EmpId=@EmpId";

            using (var db = new DBHelper())
            {
                try
                {
                    db.AddParameter("@EmpId", id);
                    var reader = db.ExecuteReader(SQLCommand);
                    if (reader.Read())
                    {
                        _EmployeeModels.EmpId = (string)reader["EmpId"];
                        _EmployeeModels.NameTh = (string)reader["NameTh"];
                        _EmployeeModels.NameEng = (string)reader["NameEng"];
                        _EmployeeModels.AddressTh = (string)reader["AddressTh"];
                        _EmployeeModels.AddressEng = (string)reader["AddressEng"];
                        _EmployeeModels.Tel = (string)reader["Tel"];
                        _EmployeeModels.dept_id = (int)reader["dept_id"];
                        _EmployeeModels.dept_name_th = (string)reader["dept_name_th"];
                        _EmployeeModels.UserName = (string)reader["UserName"];
                        _EmployeeModels.Password = Crypto.DecryptMessage(key,(string)reader["Password"]);
                        _EmployeeModels.Status = (string)reader["Status"];
                        _EmployeeModels.CreateBy = (string)reader["CreateBy"];
                        _EmployeeModels.CreateOn = (DateTime)reader["CreateOn"];
                        _EmployeeModels.UpdateBy = (string)reader["UpdateBy"];
                        _EmployeeModels.UpdateOn = (DateTime)reader["UpdateOn"];
                    }
                }
                catch (Exception ex)
                {
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0002", ex));
                }
            }
            return _EmployeeModels;
        }

        public EmployeeModels LogOn(string userName, string password)
        {
            EmployeeModels _EmployeeModels = new EmployeeModels();

            string SQLCommand = @"select TBEmployee.*,TBDepartment.dept_name_th from TBEmployee LEFT JOIN TBDepartment
                                ON TBEmployee.dept_id = TBDepartment.dept_id  where UserName =@userName and Password =@password 
                                    and TBEmployee.Status <>'Delete'";

            using (var db = new DBHelper())
            {
                try
                {
                    db.AddParameter("@userName", userName);
                    db.AddParameter("@password", Crypto.EncryptMessage(key,password));
                    var reader = db.ExecuteReader(SQLCommand);
                    if (reader.Read())
                    {
                        _EmployeeModels.EmpId = (string)reader["EmpId"];
                        _EmployeeModels.NameTh = (string)reader["NameTh"];
                        _EmployeeModels.NameEng = (string)reader["NameEng"];
                        _EmployeeModels.AddressTh = (string)reader["AddressTh"];
                        _EmployeeModels.AddressEng = (string)reader["AddressEng"];
                        _EmployeeModels.Tel = (string)reader["Tel"];
                        _EmployeeModels.dept_id = (int)reader["dept_id"];
                        _EmployeeModels.dept_name_th = (string)reader["dept_name_th"];
                        _EmployeeModels.UserName = (string)reader["UserName"];
                        _EmployeeModels.Password = Crypto.DecryptMessage(key, (string)reader["Password"]);
                        _EmployeeModels.Status = (string)reader["Status"];
                        _EmployeeModels.CreateBy = (string)reader["CreateBy"];
                        _EmployeeModels.CreateOn = (DateTime)reader["CreateOn"];
                        _EmployeeModels.UpdateBy = (string)reader["UpdateBy"];
                        _EmployeeModels.UpdateOn = (DateTime)reader["UpdateOn"];
                    }
                }
                catch (Exception ex)
                {
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0002", ex));
                    
                }
            }
           
            
            return _EmployeeModels;
        }

                
        public bool Create(EmployeeModels Employee)
        {
            bool result = true;
            string SQLCommand = @"DECLARE @STRYEAR	varchar(4)
	                            DECLARE @STRDOCNO_1	varchar(6)
	                            DECLARE @LASTNO		smallint
	                            SET @STRYEAR = RIGHT(convert(varchar(4),YEAR(getdate())) ,2)
	                            SET @LASTNO = (SELECT  isnull(max(right(isnull(EmpId ,0),3)),0)+1 FROM TBEmployee ) 
	                            SET @STRDOCNO_1='E'+@STRYEAR + STUFF('000', len('000')-(len(@LASTNO)-1), len(@LASTNO),@LASTNO)
                                INSERT into TBEmployee
                                values (@STRDOCNO_1,@NameTh,@NameEng,@AddressTh,@AddressEng,@Tel,@dept_id,@UserName,@Password,@Status,@UserId,
                                GETDATE(),@UserId,GETDATE())";

            using (var db = new DBHelper())
            {
                try
                {
                    db.BeginTransaction();
                    db.AddParameter("@NameTh", Employee.NameTh);
                    db.AddParameter("@NameEng", Employee.NameEng);
                    db.AddParameter("@AddressTh", Employee.AddressTh);
                    db.AddParameter("@AddressEng", Employee.AddressEng);
                    db.AddParameter("@Tel", Employee.Tel);
                    db.AddParameter("@dept_id", Employee.dept_id);
                    db.AddParameter("@UserName", Employee.UserName);
                    db.AddParameter("@Password", Crypto.EncryptMessage(key,Employee.Password));

                    db.AddParameter("@Status", "Active");
                    db.AddParameter("@UserID", Employee.CreateBy);

                    db.ExecuteNonQuery(SQLCommand, DbConnectionState.KeepOpen);
                    db.CommitTransaction();
                }
                catch (Exception ex)
                {
                    db.RollbackTransaction();
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0003", ex));
                    return false;
                }
            }
            return result;
        }

        public bool Edit(EmployeeModels Employee)
        {
            bool result = true;
            string SQLCommand = @"Update TBEmployee 
                                    Set NameTh=@NameTh,
                                        NameEng=@NameEng,
                                        AddressTh=@AddressTh,
                                        AddressEng=@AddressEng,
                                        Tel=@Tel,dept_id=@dept_id,UserName=@UserName,Password=@Password,
                                        UpdateBy=@UserId,UpdateOn=getdate()
                                    where EmpId=@EmpId";

            using (var db = new DBHelper())
            {
                try
                {
                    db.BeginTransaction();
                    db.AddParameter("@EmpId", Employee.EmpId);
                    db.AddParameter("@NameTh", Employee.NameTh);
                    db.AddParameter("@NameEng", Employee.NameEng);
                    db.AddParameter("@AddressTh", Employee.AddressTh);
                    db.AddParameter("@AddressEng", Employee.AddressEng);
                    db.AddParameter("@Tel", Employee.Tel);
                    db.AddParameter("@dept_id", Employee.dept_id);
                    db.AddParameter("@UserName", Employee.UserName);
                    db.AddParameter("@Password", Crypto.EncryptMessage(key, Employee.Password));
                    db.AddParameter("@UserID", Employee.UpdateBy );

                    db.ExecuteNonQuery(SQLCommand, DbConnectionState.KeepOpen);
                    db.CommitTransaction();
                }
                catch (Exception ex)
                {
                    db.RollbackTransaction();
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0004", ex));
                    return false;
                }
            }
            return result;
        }

        public bool Delete(EmployeeModels Employee)
        {
            bool result = true;
            string SQLCommand = @"Update TBEmployee
                                    Set Status=@Status,UpdateBy=@UserId,UpdateOn=getdate()
                                    where EmpId=@EmpId";

            using (var db = new DBHelper())
            {
                try
                {
                    db.BeginTransaction();
                    db.AddParameter("@EmpId", Employee.EmpId);
                    db.AddParameter("@Status", "Delete");
                    db.AddParameter("@UserID", Employee.UpdateBy);

                    db.ExecuteNonQuery(SQLCommand, DbConnectionState.KeepOpen);
                    db.CommitTransaction();
                }
                catch (Exception ex)
                {
                    db.RollbackTransaction();
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0005", ex));
                    return false;
                }
            }
            return result;
        }
    }
}